package org.ccay.core.orm.impl;

import java.io.UnsupportedEncodingException;
import java.net.URLDecoder;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.ccay.core.exception.impl.CommonSystemException;
import org.ccay.core.log.ILogger;
import org.ccay.core.log.CcayLoggerFactory;
import org.ccay.core.util.DateUtil;
import org.ccay.core.util.EncodingUtil;

public final class FilterBuildMySqlUtils {
	/**
	 * 对应数据字段的类型
	 */
	public static final String VARCHAR = "VARCHAR";
	public static final String NUMERIC = "NUMERIC";
	public static final String DATE = "DATE";
	public static final String TIMESTAMP = "TIMESTAMP";
	
	
	/**
	 * 算术运算对应的标示
	 */
	public static final int EQUALS = 0;
	public static final int NOT_EQUALS = 1;
	public static final int LARGE = 2;
	public static final int LESS = 3;
	public static final int LARGE_EQUALS = 4;
	public static final int LESS_EQUALS = 5;
	public static final int INCLUDE = 6;
	public static final int NOT_INCLUDE = 7;
	public static final int BEFORE = 8;
	public static final int AFTER = 9;
	public static final int NULL = 10;
	public static final int NOT_NULL = 11;
	
	//定义日期时间格式为：yyyy-MM-dd'T'HH:mm:ss.SSSZ，包括毫秒数
	public static final SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss.SSSZ");
	
	//定义日期格式为：yyyy-MM-dd
	public static final String dateFormatStr = "%Y-%m-%d";
	
	//定义日期时间格式，时间以24小时制
	public static final String dateTimeFormatStr = "%Y-%m-%d %H:%i:%s";
	
	//TO_CHAR函数定义
	public static final String toCharStr = "DATE_FORMAT";
	
	//TO_DATE函数定义
	public static final String toDateStr = "STR_TO_DATE";

	public static final Map<Integer,FilterBuildSql> filterTypeMap = new HashMap<Integer,FilterBuildSql>();
	
	private static final ILogger logger = CcayLoggerFactory.getLogger(FilterBuildMySqlUtils.class); 
	/**
	 * 
	 * 根据0-9对应的类型，如1对应'=' or 'in',返回对应的SQL片段
	 * 
	 */
	static {
		//=的sql实现
		filterTypeMap.put(EQUALS, new EqualsOrNotEquals4Object() {

			protected String getSymbol() {
				return " = ";
			}
		});
		//!=的sql实现
		filterTypeMap.put(NOT_EQUALS, new EqualsOrNotEquals4Object() {

			protected String getSymbol() {
				return " != ";
			}
		});
		//>的sql实现
		filterTypeMap.put(LARGE, new CommonSymbol() {
			protected String getSymbol() {
				return " > ";
			}

		});
		//<的sql实现
		filterTypeMap.put(LESS, new CommonSymbol() {
			protected String getSymbol() {
				return " < ";
			}

		});
		//>=的sql实现
		filterTypeMap.put(LARGE_EQUALS, new CommonSymbol() {
			protected String getSymbol() {
				return " >= ";
			}

		});
		//<=的sql实现
		filterTypeMap.put(LESS_EQUALS, new CommonSymbol() {
			protected String getSymbol() {
				return " <= ";
			}
		});
		//包含的sql实现
		filterTypeMap.put(INCLUDE, new FilterBuildSql() {
			public String buildSqlFragment(String field, String dataType, Object filterValue) {
				return !VARCHAR.equals(dataType) ? "" : new StringBuffer()
						.append("LOWER(")
						.append(field)
						.append(") LIKE LOWER(CONCAT('%' , TRIM('")
						.append(decoderDelimitedString(filterValue)).append("') , '%'))")
						.toString();
			}
		});
		//不包含的sql实现
		filterTypeMap.put(NOT_INCLUDE, new FilterBuildSql() {
			public String buildSqlFragment(String field, String dataType, Object filterValue) {
				return !VARCHAR.equals(dataType) ? "" : new StringBuffer()
						.append("LOWER(")
						.append(field)
						.append(") NOT LIKE LOWER(CONCAT('%' , TRIM('")
						.append(decoderDelimitedString(filterValue)).append("') , '%'))")
						.toString();
			}
		});
		//前置的sql实现
		filterTypeMap.put(BEFORE, new FilterBuildSql() {
			public String buildSqlFragment(String field, String dataType, Object filterValue) {
				return !VARCHAR.equals(dataType) ? "" : new StringBuffer()
						.append("LOWER(")
						.append(field)
						.append(") LIKE LOWER( CONCAT(TRIM('")
						.append(decoderDelimitedString(filterValue)).append("') , '%'))")
						.toString();
			}
		});
		//后置的sql实现
		filterTypeMap.put(AFTER, new FilterBuildSql() {
			public String buildSqlFragment(String field, String dataType, Object filterValue) {
				return !VARCHAR.equals(dataType) ? "" : new StringBuffer()
						.append("LOWER(")
						.append(field)
						.append(") LIKE LOWER(CONCAT('%' , TRIM('")
						.append(decoderDelimitedString(filterValue)).append("')))").toString();
			}
		});
		// null的sql实现
		filterTypeMap.put(NULL, new FilterBuildSql() {
			public String buildSqlFragment(String field, String dataType, Object filterValue) {
				return field + " is null ";
			}
		});
		//not null的sql实现
		filterTypeMap.put(NOT_NULL, new FilterBuildSql() {
			public String buildSqlFragment(String field, String dataType, Object filterValue) {
				return field+ " is not null ";
			}
		});
		

	}
	
	/**
	 * 
	 * 转换值中包含的"'"为"''"
	 * @param filterValue
	 * @return
	 */
	private static String delimitedString(Object filterValue) {
		if(filterValue == null){
			return "";
		}
		return filterValue.toString().indexOf("'") == -1 ? filterValue
				.toString() : filterValue.toString().replaceAll("'", "''");
	}

	/**
	 * 
	 * 转换值中包含的"'"为"''"
	 * @param filterValue
	 * @return
	 */
	@SuppressWarnings("deprecation")
	private static String decoderDelimitedString(Object filterValue) {
		if(filterValue == null){
			return "";
		}
		String value = null;
		try {
			value = URLDecoder.decode(filterValue.toString(),EncodingUtil.UTF_8);
		} catch (UnsupportedEncodingException e) {
			throw new CommonSystemException("URLDecoder.decode error",e);
		}

		return value.indexOf("'") == -1 ? value : value.replaceAll("'", "''");
	}
	/**
	 * 
	 * @Created 2013-07-19
	 */
	public interface FilterBuildSql {
		//构建SQL片段，如：t.sbirthday <= to_date('2013-07-25','yyyy-MM-dd')等
		String buildSqlFragment(String field, String dataType, Object filterValue);
	}

	/**
	 * 针对 '>'、 '>='、 '<'、 '<=' 符号进行抽象
	 * 
	 * 
	 * @Created 2013-07-19
	 */
	private static abstract class CommonSymbol implements FilterBuildSql {
		/*
		 * (non-Javadoc)
		 * 
		 * @see
		 * org.ccay.core.orm.impl.DynamicSqlUtils.DynamicSql#sql
		 * (java.lang.String, java.lang.Object)
		 */
		public String buildSqlFragment(String field, String dataType, Object filterValue) {
			if(filterValue == null){
				return field + "is null";
			}else if("".equals(filterValue.toString())){
				return field + getSymbol() + "''";
			}
			if (NUMERIC.equals(dataType)) {
				Double valueOfNmuber = Double.valueOf(filterValue.toString());
				return field + getSymbol() + valueOfNmuber;
			}
			if (DATE.equals(dataType) || TIMESTAMP.equals(dataType)) {
				Date valueOfDate = null;
				String fix = "'";
				String leftParenthesis = "(";
				String rightParenthesis = ")";
				String comma = ",";
				String trunc = "TRUNC";
				StringBuilder sBuilder=new StringBuilder();
				//日期时间格式
				String format = null;
				//转换成24小时格式数字串
				String valueOfString = null;
				try {
					 valueOfDate =  dateFormat.parse(filterValue.toString());
				} catch (ParseException e) {
					logger.error(e);
				}
				if(DATE.equals(dataType)){
					format = dateFormatStr;
					valueOfString = DateUtil.dateToDateString(valueOfDate);
					return sBuilder.append(toCharStr).append(leftParenthesis).append(field)
				    .append(comma).append(fix).append(format).append(fix)
				    .append(rightParenthesis).append(getSymbol())
					.append(fix).append(valueOfString).append(fix)
					//.append(comma).append(fix).append(format).append(fix)
					.toString();
					
				}else if(TIMESTAMP.equals(dataType)){
					format = dateTimeFormatStr;
					valueOfString = DateUtil.date24ToString(valueOfDate);
					return sBuilder.append(field).append(getSymbol())
							.append(toDateStr).append(leftParenthesis).append(fix)
							.append(valueOfString).append(fix)
							.append(comma).append(fix).append(format).append(fix)
							.append(rightParenthesis).toString();
				}
				//t.sbirthday = TO_DATE('2013-08-04','yyyy-MM-dd') || t.creation_date = TO_DATE('2012-8-10 17:31:19','yyyy-MM-dd HH24:MI:SS')
//				return new StringBuilder()
//					.append(field).append(getSymbol())
//					.append(toDateStr).append(leftParenthesis).append(fix)
//					.append(valueOfString).append(fix)
//					.append(comma).append(fix).append(format).append(fix)
//					.append(rightParenthesis).toString();
				
			}
			return "";
		}

		/**
		 * 返回格式后的SQL字串
		 * @return
		 */
		abstract String getSymbol();
	}

	/**
	 * 
	 * 针对 '='、 '!='符号进行抽象，值为多个时，采用 'in' 、'not in'
	 * 
	 * @Created 2013-07-19
	 */
	private static abstract class EqualsOrNotEquals4Object implements FilterBuildSql {
		public String buildSqlFragment(String field, String dataType, Object filterValue) {
			if(filterValue == null){
				return field + getSymbol() + "''";
			}
			StringBuilder sb = new StringBuilder(256);
			String fix = null;
			String tempSql = null;
			if (VARCHAR.equals(dataType)) {
				fix = "'";
				tempSql = decoderDelimitedString(filterValue);
			} else if (NUMERIC.equals(dataType)) {
				fix = "";
				tempSql = delimitedString(filterValue);
			} else{
				fix = "'";
				Date valueOfDate = null;
				String leftParenthesis = "(";
				String rightParenthesis = ")";
				String comma = ",";
				String trunc = "TRUNC";
				StringBuilder sBuilder=new StringBuilder();
				//日期时间格式
				String format = null;
				//转换成24小时格式数字串
				String valueOfString = null;
				try {
					 valueOfDate =  dateFormat.parse(filterValue.toString());
				} catch (ParseException e) {
					logger.error(e);
				}
				if(DATE.equals(dataType)){
					format = dateFormatStr;
					valueOfString = DateUtil.dateToDateString(valueOfDate);
					return sBuilder.append(toCharStr).append(leftParenthesis).append(field)
						    .append(comma).append(fix).append(format).append(fix)
						    .append(rightParenthesis).append(getSymbol())
							.append(fix).append(valueOfString).append(fix)
							//.append(comma).append(fix).append(format).append(fix)
							.toString();
				}else if(TIMESTAMP.equals(dataType)){
					format = dateTimeFormatStr;
					valueOfString = DateUtil.date24ToString(valueOfDate);
					return sBuilder.append(field).append(getSymbol())
							.append(toDateStr).append(leftParenthesis).append(fix)
							.append(valueOfString).append(fix)
							.append(comma).append(fix).append(format).append(fix)
							.append(rightParenthesis).toString();
				}
				//t.sbirthday = TO_DATE('2013-08-04','yyyy-MM-dd') || t.creation_date = TO_DATE('2012-8-10 17:31:19','yyyy-MM-dd HH24:MI:SS')
//				return new StringBuilder()
//					.append(field).append(getSymbol())
//					.append(toDateStr).append(leftParenthesis).append(fix)
//					.append(valueOfString).append(fix)
//					.append(comma).append(fix).append(format).append(fix)
//					.append(rightParenthesis).toString();
			}
			sb.append(field);
			sb.append(getSymbol());
			sb.append(fix);
			sb.append(tempSql);
			sb.append(fix);
			return sb.toString();
		}

		/**
		 * 返回格式后的SQL字串
		 * @return
		 */
		abstract String getSymbol();
	}
	
	
}
